﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyHocPhi.Entity;

/// <summary>
/// Summary description for VAI_TRODAO
/// </summary>
namespace QuanLyHocPhi.DAL
{
    public class VAI_TRO_DAO
    {
        private const string SQL_INSERT = "INSERT INTO [VAI_TRO](TEN_VAI_TRO) VALUES(@TEN_VAI_TRO)"; 
        private const string SQL_UPDATE = "update VAI_TRO set TEN_VAI_TRO = @TEN_VAI_TRO where MA_VAI_TRO @MA_VAI_TRO";
        private const string SQL_DELETE = "DELETE FROM VAI_TRO WHERE MA_VAI_TRO = @MA_VAI_TRO";
        private const string SQL_SELECT_ALL = "SELECT * FROM [VAI_TRO]";
        private const string SQL_SELECT_VAI_TRO_ID = "select MA_VAI_TRO from [VAI_TRO] where TEN_VAI_TRO = @TEN_VAI_TRO";

        // Parameters
        private const string PARM_VAI_TRO_ID = "@MA_VAI_TRO";
        private const string PARM_VAI_TRO_NAME = "@TEN_VAI_TRO";

        #region Public Methods
        public IList<VAI_TRO> GetAll()
        {
            IList<VAI_TRO> VAI_TROs = new List<VAI_TRO>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_ALL, null))
            {
                while (reader.Read())
                {
                    VAI_TRO VAI_TRO = new VAI_TRO();
                    VAI_TRO.MA_VAI_TRO = reader.GetInt32(reader.GetOrdinal("MA_VAI_TRO"));
                    VAI_TRO.TEN_VAI_TRO = reader.GetString(reader.GetOrdinal("TEN_VAI_TRO"));
                    VAI_TROs.Add(VAI_TRO);
                }
            }
            return VAI_TROs;
        }

        public IList<int> GetMA_VAI_TRO(string TEN_VAI_TRO)
        {
            IList<int> list = new List<int>();
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_VAI_TRO_NAME, SqlDbType.NVarChar)};
            parms[0].Value = TEN_VAI_TRO;
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_VAI_TRO_ID, parms))
            {
                while (reader.Read())
                {
                    int MA_VAI_TRO = reader.GetInt32(reader.GetOrdinal("MA_VAI_TRO"));
                    list.Add(MA_VAI_TRO);
                }
            }
            return list;
        }

        public Boolean Insert(VAI_TRO VAI_TRO)
        {
            {
                SqlParameter[] parms;
                parms = new SqlParameter[] {
											 new SqlParameter(PARM_VAI_TRO_NAME, SqlDbType.NVarChar)};
                parms[0].Value = VAI_TRO.TEN_VAI_TRO;

                try
                {
                    int result = 0;
                    SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                    result = SQLDBHelper.ExecuteNonQuery(conn,
                        CommandType.Text, SQL_INSERT, parms);
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    return (result > 0 ? true : false);
                }
                catch (Exception e)
                {
                    throw e;
                }
                //return false;
            }
        }

        public bool Update(VAI_TRO VAI_TRO)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] {
											 new SqlParameter(PARM_VAI_TRO_NAME, SqlDbType.NVarChar),
                                             new SqlParameter(PARM_VAI_TRO_ID, SqlDbType.Int)};
            parms[0].Value = VAI_TRO.TEN_VAI_TRO;
            parms[1].Value = VAI_TRO.MA_VAI_TRO;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_UPDATE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }

        public bool Delete(int id)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARM_VAI_TRO_ID, SqlDbType.Int) };
            parms[0].Value = id;

            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                    CommandType.Text, SQL_DELETE, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                throw e;
            }
            //return false;
        }
        #endregion
    }
}